--企业表 ent_info
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ent_info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ent_info]
GO

CREATE TABLE [dbo].[ent_info] (
	[ent_id] [int] IDENTITY (1, 1) NOT NULL ,
	[ent_type] [int] default 0,
	ent_domain varchar(100),
	ent_name varchar(50),
	[ent_status] [int] default 1 --是否参报表生成 0参与 1为不参与
) ON [PRIMARY]
GO

--基础数据表 alexa_base_data 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[alexa_base_data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[alexa_base_data]
GO

CREATE TABLE [dbo].[alexa_base_data] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[ent_id] [int] NOT NULL ,
	[traffic_rank] [int] default 0,
	[traffic_rank_cn] [int] default 0,
	[site_linking_in] [int] default 0,
	[avg_load_time] [numeric](18, 3) default 0,
	[pageviews_global_percent] [numeric](18, 8) default 0,--独立访客数UV比例
	[pageviews_per_user] [numeric](18, 1) default 0,--页面访问量PV比例
	[ip_count] [int] default 0,
	[pv_count] [int] default 0,
	[seo_referer_percentage] [numeric](18, 8) default 0,--来源搜索引擎
	[growth_movement] [int] default 0,
	type varchar(2) ,--w代表:周 m代表:月 3m代表(季度):3个月
	stat_start_time varchar(20) default '',--统计的开始时间
	stat_end_time varchar(20) default '',--统计的结束时间
	[sub_domain_spread] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL,--子域名
	[create_date] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

--年度数据统计数据 alexa_year_data 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[alexa_year_data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[alexa_year_data]
GO

CREATE TABLE [dbo].[alexa_year_data] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[ent_id] [int] NOT NULL ,
	[traffic_rank] [int] default 0,
	[traffic_rank_cn] [int] default 0,
	[site_linking_in] [int] default 0,
	[avg_load_time] [numeric](18, 3) default 0,
	[pageviews_global_percent] [numeric](18, 8) default 0,--独立访客数UV比例
	[pageviews_per_user] [numeric](18, 1) default 0,--页面访问量PV比例
	[ip_count] [int] default 0,
	[pv_count] [int] default 0,
	[seo_referer_percentage] [numeric](18, 8) default 0,--来源搜索引擎
	[growth_movement] [int] default 0,
	type varchar(2) ,--6m代表:半年度 y代表:年度
	stat_start_time varchar(20) default '',--统计的开始时间
	stat_end_time varchar(20) default '',--统计的结束时间
	[create_date] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

--alexa报表生成表 alexa_report
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[alexa_report]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[alexa_report]
GO

CREATE TABLE [dbo].[alexa_report] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	type varchar(2) ,--w代表:周 m代表:月 3m代表:3个月(季度),6m代表:半年度 y代表:年度
	typeRemark varchar(20),
	summary varchar(100),
	stat_start_time varchar(20) default '',--统计的开始时间
	stat_end_time varchar(20) default '',--统计的结束时间
	[create_date] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO